package com.project.CourseManager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import android.app.ProgressDialog;
import android.content.Context;
import android.os.AsyncTask;
import android.os.Bundle;
import android.support.v4.app.ListFragment;
import android.view.LayoutInflater;
import android.view.View;
import android.view.ViewGroup;
import android.widget.ListAdapter;
import android.widget.SimpleAdapter;

import com.project.Library.Connect;
import com.project.trac.R;

public class test_Search extends ListFragment {
	
	String SECT;
	
	public static ListFragment newInstance(Context context) {
		test_Search lf = new test_Search();
		return lf;
	}

	public View onCreateView(LayoutInflater inflater, ViewGroup container,
			Bundle savedInstanceState) {
		ViewGroup root = (ViewGroup) inflater.inflate(
				R.layout.generic_list, null);
		
		cf.setRemoteIP("192.168.20.2");
		cf.setRemotePort(3306);
		cf.setRemoteDB("db_thesis");

		
		new innerTask().execute();
		
		return root;
	}
	
	
	
	
	
	
	
	
	Connect cf = new Connect();
	
	private class innerTask extends AsyncTask<String, String, String> {

		HashMap<String, String> hashmap = new HashMap<String, String>();
		ArrayList<HashMap<String, String>> collection = new ArrayList<HashMap<String, String>>();

		ProgressDialog dialog;
		ListAdapter listAdapter;

		String KEY_CAT = "CatalogNumber";
		String KEY_SET = "CatalogDetails";

		String CAT = " ";
		String SET = " ";

		Connection conn;
		ResultSet rs;

		protected void onPreExecute() {
			super.onPreExecute();
			/* Debugging Messages */
			// System.out.println("------------------ onPreExecute call");

			dialog = new ProgressDialog(getActivity());
			dialog.setMessage("Retrieving the file list, please wait.");
			dialog.setIndeterminate(true);
			dialog.setCancelable(true);
			dialog.show();

		}

		protected String doInBackground(String... args) {

			/* Debugging Messages */
			//System.out.println("------------------ doInBackground call");

			try {
				if (conn == null) {
					/* Debugging Messages */
					System.out.println("Connection is " + conn + "!\nOpening new connection!");

					Class.forName(cf.getDriverClass()).newInstance();
					cf.setConn((Connection) DriverManager.getConnection(cf.getRemoteURL(), cf.getUsername(), cf.getPassword()));
					cf.setStmt(cf.getConn().createStatement());

					rs = cf.getStmt().executeQuery(
							"SELECT " +
									"tb_subjects.UserId, tb_subjectlist.CatalogNumber, Section, " +
									"Room, SubjectTime, " +
									"Title, tb_users.FirstName, LastName " +
									"FROM tb_subjects " +
									"INNER JOIN tb_subjectlist ON tb_subjects.SubjectName = tb_subjectlist.SubjectName " +
							"INNER JOIN tb_users ON tb_users.UserId = tb_subjects.UserId");

					/* Debugging Messages : check if RS is empty */
					//System.out.println("RS ---------------: " + rs);

					/* Debugging Messages */
					System.out.println("Now connected to database!");

					while (rs.next()) {
						CAT = rs.getString("CatalogNumber");
						SET = rs.getString("Section") + "\n" +
								" "+ "\n" +
								rs.getString("SubjectTime")  + "\n" +
								rs.getString("Room") + "\n" +
								rs.getString("Title")  + " " + rs.getString("FirstName")  + " " + rs.getString("LastName");


						hashmap = new HashMap<String, String>();
						hashmap.put(KEY_CAT, CAT);
						hashmap.put(KEY_SET, SET);

						// adding HashMap to ArrayList
						collection.add(hashmap);

						/* Debugging Messages : print check for HashMap values */
						//System.out.println(hashmap);

					}

				} else {
					// TODO when con != null
					/* Debugging Messages */
					// System.out.println("Connection is still open!");
				}
			} catch (SQLException e) {
				System.out.println("doInBackground: ** SQLException ** ");
				System.err.println(e);
			} catch (InstantiationException e) {
				System.out.println("doInBackground: ** Instantiation Exception ** ");
				System.err.println(e);
			} catch (IllegalAccessException e) {
				System.out.println("doInBackground: ** Illegal Access Exception ** ");
				System.err.println(e);
			} catch (ClassNotFoundException e) {
				System.out.println("doInBackground: ** Class Not Found Exception ** ");
				System.err.println(e);
			} catch (Exception e) {
				System.out.println("doInBackground: ** Another Instantiation Exception ** ");
				System.err.println(e);
			} finally {
				try {
					cf.getConn().close();
				} catch (SQLException e) {
					System.out.println("doInBackground/finally: ** SQLException ** ");
					System.err.println(e);
				}
			}

			return null;
		}


		protected void onPostExecute(String postExecute) {
			/** Debugging Messages */
			System.out.println("------------------ onPostExecute call");

			dialog.dismiss();

			getActivity().runOnUiThread(new Runnable() {

				@Override
				public void run() {

					listAdapter = new SimpleAdapter(getActivity(), collection,
							R.layout.d1a_search_result_detail, 
							new String[] { KEY_CAT, KEY_SET}, 
							new int[] { R.id.r_key_cat_new, R.id.r_key_set});

					setListAdapter(listAdapter);
				}
			});
		}
	} 

}